package models;

import java.awt.Dialog;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.swing.JOptionPane;
import javax.swing.table.AbstractTableModel;

public class ResultSetTableModel extends AbstractTableModel 
{
   public final Connection connection;
   private final Statement statement;
   private ResultSet resultSet;
   private ResultSetMetaData metaData;
   private int numberOfRows;
   private PreparedStatement pstmt;

   private boolean connectedToDatabase = false;
   
   public ResultSetTableModel(Connection connection, String query ) 
      throws SQLException, ClassNotFoundException
   {         
      
      this.connection = connection;
             
      statement = this.connection.createStatement( 
         ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_READ_ONLY );

      connectedToDatabase = true;

       setDefaultQuery(query);
   } 

   
   @Override
   public Class getColumnClass( int column ) throws IllegalStateException
   {
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );

      try 
      {
         String className = metaData.getColumnClassName( column + 1 );
         return Class.forName( className );
      } 
      catch ( Exception exception ) 
      {
         exception.printStackTrace();
      } 
      
      return Object.class; 
   } 


   public int getColumnCount() throws IllegalStateException
   {   
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );

      try 
      {
         return metaData.getColumnCount(); 
      } 
      catch ( SQLException sqlException ) 
      {
         sqlException.printStackTrace();
      } 
      
      return 0; 
   } 

   
   public String getColumnName( int column ) throws IllegalStateException
   {    
     
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );

      try 
      {
         return metaData.getColumnName( column + 1 );  
      } 
      catch ( SQLException sqlException ) 
      {
         sqlException.printStackTrace();
      } 
      
      return ""; 
   } 

  
   public int getRowCount() throws IllegalStateException
   {      
    
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );
 
      return numberOfRows;
   } 

  
   public Object getValueAt( int row, int column ) 
      throws IllegalStateException
   {
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );
     
      try 
      {
         resultSet.absolute( row + 1 );
         return resultSet.getObject( column + 1 );
      } 
      catch ( SQLException sqlException ) 
      {
         sqlException.printStackTrace();
      } 
      
      return ""; 
   } 
   
   public void setDefaultQuery( String query ) 
      throws SQLException, IllegalStateException 
   {
     
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );

      resultSet = statement.executeQuery( query );

      metaData = resultSet.getMetaData();

      resultSet.last();                   
      numberOfRows = resultSet.getRow();  
      
      // notify JTable that model has changed
      fireTableStructureChanged();
   }

      public void setSearch(String nome, int genero, int tipo) throws SQLException {
      connection.setAutoCommit(false);
      try {
           pstmt = connection.prepareStatement("SELECT nome_item AS Título, nome_acervo_tipo AS Categoria, nome_genero AS Gênero FROM acervo a JOIN generos g ON g.id_genero = a.id_genero JOIN acervo_tipos act ON act.id_acervo_tipo = a.id_acervo_tipo WHERE a.nome_item LIKE ? AND a.id_genero = ? AND a.id_acervo_tipo = ? ORDER BY a.nome_item");

           pstmt.setString(1, '%'+nome+'%');                
           pstmt.setInt(2, genero+1);
           pstmt.setInt(3, tipo+1);       
           resultSet = pstmt.executeQuery();
           metaData = resultSet.getMetaData();
           resultSet.last();
           numberOfRows = resultSet.getRow();
           fireTableStructureChanged();       
           connection.commit();
       
       }
       catch (Exception ex) {
           System.out.println(ex);
           connection.rollback();
       }
       connection.setAutoCommit(true);
   }
   
   public void setInsert(int genero, int tipo, String nome) throws SQLException {
       connection.setAutoCommit(false);
       try {
           pstmt = connection.prepareStatement("INSERT INTO acervo (id_genero, id_acervo_tipo, nome_item, ind_disponivel, preco) VALUES (?,?,?,'S',5.00)");
           
           pstmt.setInt(1, genero+1);
           pstmt.setInt(2, tipo+1);
           pstmt.setString(3, nome);
           
           pstmt.executeUpdate();
           connection.commit();
           JOptionPane.showMessageDialog(null, "Registro inserido!");
       }
       catch (Exception ex) {
           System.out.println(ex);
           connection.rollback();
       }
       connection.setAutoCommit(true);
   }   
      
   public void disconnectFromDatabase()            
   {              
      if ( !connectedToDatabase )                  
         return;        
      try                                          
      {                                            
         statement.close();                        
         connection.close();                       
      } 
      catch ( SQLException sqlException )          
      {                                            
         sqlException.printStackTrace();           
      } 
      finally  
      {                                            
         connectedToDatabase = false;              
      }
   }
}


